﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
using nplJobsDAL;
using System.Data;
using npljobs.JobAlertService.Objects;

namespace npljobs.JobAlertService.Helper
{
    public class DBHepler : DataAccessBase
    {
        DataBaseHelper dbHelper;

        #region GetJobSeekerAlert
        public DataSet GetJobSeekerAlert()
        {
            StoredProcedureName = StoredProcedure.Name.spSelectJobSeekerAlert.ToString();
            dbHelper = new DataBaseHelper(StoredProcedureName);
            DataSet dsJobSeekerAlert = dbHelper.Run(base.ConnectionString);
            return dsJobSeekerAlert;
        }
        #endregion

        #region GetJobs
        internal List<Job> GetJobs(DataTable dtSkill, int cityID,int countryID,int categoryID,int jobTypeID,int salaryID,string jobTitle)
        {
            DataSet dsJob;
            List<Job> lst = new List<Job>();
            try
            {
                StoredProcedureName = StoredProcedure.Name.spSelectJobsBySkills.ToString();

                using (SqlConnection con = new SqlConnection(base.ConnectionString))
                {
                    con.Open();
                 
                    SqlCommand cmd = new SqlCommand(StoredProcedureName);
                    cmd.Connection = con;
                    cmd.Parameters.Add("@Skills", SqlDbType.Structured).Value = dtSkill;
                    cmd.CommandType = CommandType.StoredProcedure;

                    //SqlParameter paramCityID = cmd.Parameters.Add("@CityID", SqlDbType.Int);
                    //paramCityID.Direction = ParameterDirection.Input;
                    //paramCityID.Value = cityID;

                    //SqlParameter paramCountryID = cmd.Parameters.Add("@CountryID", SqlDbType.Int);
                    //paramCountryID.Direction = ParameterDirection.Input;
                    //paramCountryID.Value = countryID;

                    SqlParameter paramCategoryID = cmd.Parameters.Add("@CategoryID", SqlDbType.Int);
                    paramCategoryID.Direction = ParameterDirection.Input;
                    paramCategoryID.Value = categoryID;

                    //SqlParameter paramJobTypeID = cmd.Parameters.Add("@WorkTypeID", SqlDbType.Int);
                    //paramJobTypeID.Direction = ParameterDirection.Input;
                    //paramJobTypeID.Value = jobTypeID;

                    //SqlParameter paramSalaryID = cmd.Parameters.Add("@SalaryRangeID", SqlDbType.Int);
                    //paramSalaryID.Direction = ParameterDirection.Input;
                    //paramSalaryID.Value = salaryID;

                    SqlParameter paramJobTitle = cmd.Parameters.Add("@JobTitle", SqlDbType.NVarChar);
                    paramJobTitle.Direction = ParameterDirection.Input;
                    paramJobTitle.Value = jobTitle;

                    cmd.ExecuteNonQuery();
                    SqlDataAdapter adp = new SqlDataAdapter(cmd);
                    dsJob = new DataSet();
                    adp.Fill(dsJob);

                    con.Close();
                }
                lst = GetList(dsJob);
            }
            catch (Exception ex)
            {
            }
            return lst;
        }
        #endregion

        #region GetList
        protected List<Job> GetList(DataSet dtSkill)
        {
            List<Job> job;
            try
            {
                job = (from n in dtSkill.Tables[0].AsEnumerable()
                       select new Job
                       {
                           CompanyImage = n.Field<string>("JobImagePath"),
                           JobTitle = n.Field<string>("JobTitle"),
                           //Location = n.Field<string>("City"),
                           CompanyName = n.Field<string>("CompanyName"),
                           PostedDate = n.Field<DateTime>("PostedDate"),
                           ApplicationDateline = n.Field<DateTime>("ApplicationDateline"),
                           Skills = n.Field<string>("Skills"),
                           Qualifications = n.Field<string>("Qualifications"),
                           JobID = n.Field<Int32>("JobID"),
                           SalaryRangeMax = n.Field<decimal>("SalaryRangeMax"),
                           SalaryRangeMin = n.Field<decimal>("SalaryRangeMin")
                           //SalaryRange = n.Field<int>("SalaryRange")

                       }).ToList();

                return job;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            finally { }
        }
        #endregion

        #region GetJobSeeker
        internal JobSeeker GetJobSeekerById(Guid jobSeekerID)
        {
            DataBaseHelper dbHelper;
            DataSet dsJobSeeker;
            JobSeeker js = new JobSeeker();

            try
            {
                StoredProcedureName = StoredProcedure.Name.spSelectJobSeekerByID.ToString();

                SelectJobSeekerParameters selectJobSeekerParameters = new SelectJobSeekerParameters(jobSeekerID);
                dbHelper = new DataBaseHelper(StoredProcedureName);
                dbHelper.Parameters = selectJobSeekerParameters.Parameters;
                dsJobSeeker = dbHelper.Run(base.ConnectionString, dbHelper.Parameters);
                js = GetJobSeekerList(dsJobSeeker);
            }
            catch (Exception ex)
            {
            }
            return js;
        }
        #endregion

        #region SQL parameters for selecting job seeker by id
        public class SelectJobSeekerParameters
        {
            public int CkeckUnique;
            private SqlParameter[] _parameters;

            public SelectJobSeekerParameters(Guid jobSeekerID)
            {
                this.JobSeekerID = jobSeekerID;
                Build();
            }

            private void Build()
            {
                SqlParameter[] parameters = 
                {
                    new SqlParameter("@JobSeekerID", JobSeekerID),
                };

                Parameters = parameters;
            }

            public Guid JobSeekerID { get; set; }

            public SqlParameter[] Parameters
            {
                get { return _parameters; }
                set { _parameters = value; }
            }
        }
        #endregion

        #region GetJobSeekerList
        private JobSeeker GetJobSeekerList(DataSet dsJobSeeker)
        {
            List<JobSeeker> jobSeeker;
            try
            {
                jobSeeker = (from n in dsJobSeeker.Tables[0].AsEnumerable()
                             select new JobSeeker
                             {
                                 JobSeekerID = n.Field<Guid>("JobSeekerID"),
                                 FirstName = n.Field<string>("FirstName"),
                                 LastName = n.Field<string>("LastName"),
                                 Email = n.Field<string>("Email"),
                                 AlternativeEmail = n.Field<string>("AlternativeEmail"),
                                 DOB = n.Field<DateTime>("DOB"),

                                 GenderID = n.Field<int>("GenderID"),
                                 MaritalStatusID = n.Field<int>("MaritalStatusID"),
                                 NationalityID = n.Field<int>("NationalityID"),
                                 PostalAddress = n.Field<string>("PostalAddress"),
                                 //PermanentAddress = n.Field<string>("PermanentAddress"),
                                 HomePhone = n.Field<string>("HomePhone"),
                                 MobilePhone = n.Field<string>("MobilePhone"),
                                 Photo = n.Field<string>("Photo"),
                                 Resume = n.Field<string>("Resume"),
                                 IsDeleted = n.Field<bool>("IsDeleted"),
                                 AboutMe = n.Field<string>("AboutMe"),

                                 CreatedOn = n.Field<DateTime>("CreatedOn"),
                                 Modifiedon = n.Field<DateTime>("Modifiedon"),

                                 Skills = n.Field<string>("Skills"),
                                 Qualifications = n.Field<string>("Qualifications"),

                                 ExpectedSalaryID = n.Field<int>("ExpectedSalaryID"),
                                 PresentSalaryID = n.Field<int>("PresentSalaryID"),

                                 CountryID = n.Field<int?>("CountryID"),
                                 DivisionID = n.Field<int?>("DivisionID"),
                                 DistrictID = n.Field<int?>("DistrictID"),
                                 CityID = n.Field<int?>("CityID"),

                             }).ToList();

                return jobSeeker.FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            finally { }
        }
        #endregion

        #region CheckAlertStatus
        internal bool CheckAlertStatus(Guid jobSeekerID, int jobAlertID, int frequencyID)
        {
            bool isExists = false;

            using (SqlConnection sqlCon = new SqlConnection(base.ConnectionString))
            {
                sqlCon.Open();
                StoredProcedureName = StoredProcedure.Name.spCheckAlertStatus.ToString();

                using (SqlCommand cmd = new SqlCommand(StoredProcedureName))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = sqlCon;

                    SqlParameter paramJobSeekerID = cmd.Parameters.Add("@JobSeekerID", SqlDbType.UniqueIdentifier);
                    paramJobSeekerID.Direction = ParameterDirection.Input;
                    paramJobSeekerID.Value = jobSeekerID;

                    SqlParameter paramJobAlertID = cmd.Parameters.Add("@JobAlertID", SqlDbType.Int);
                    paramJobAlertID.Direction = ParameterDirection.Input;
                    paramJobAlertID.Value = jobAlertID;

                    SqlParameter paramFrequencyID = cmd.Parameters.Add("@FrequencyID", SqlDbType.Int);
                    paramFrequencyID.Direction = ParameterDirection.Input;
                    paramFrequencyID.Value = frequencyID;

                    var value = cmd.ExecuteScalar();

                    isExists = value != null ? int.Parse(value.ToString()) == 1 ? true : false : false;

                }
                sqlCon.Close();
            }

            return isExists;
        } 
        #endregion

        #region SaveJobSeekerAlertStatus
        internal void SaveJobSeekerAlertStatus(Guid jobSeekerID, int jobAlertID, int frequencyID)
        {
            try
            {
                StoredProcedureName = StoredProcedure.Name.spSaveJobSeekerAlertStatus.ToString();

                using (SqlConnection sqlCon = new SqlConnection(base.ConnectionString))
                {
                    sqlCon.Open();

                    using (SqlCommand cmd = new SqlCommand(StoredProcedureName))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Connection = sqlCon;

                        SqlParameter paramJobSeekerID = cmd.Parameters.Add("@JobSeekerID", SqlDbType.UniqueIdentifier);
                        paramJobSeekerID.Direction = ParameterDirection.Input;
                        paramJobSeekerID.Value = jobSeekerID;

                        SqlParameter paramJobAlertID = cmd.Parameters.Add("@JobAlertID", SqlDbType.Int);
                        paramJobAlertID.Direction = ParameterDirection.Input;
                        paramJobAlertID.Value = jobAlertID;

                        SqlParameter paramFrequencyID = cmd.Parameters.Add("@FrequencyID", SqlDbType.Int);
                        paramFrequencyID.Direction = ParameterDirection.Input;
                        paramFrequencyID.Value = frequencyID;

                        SqlParameter paramTimestamp = cmd.Parameters.Add("@Timestamp", SqlDbType.DateTime);
                        paramTimestamp.Direction = ParameterDirection.Input;
                        paramTimestamp.Value = DateTime.Now;

                        cmd.ExecuteNonQuery();
                    }
                    sqlCon.Close();
                }
            }
            catch (Exception)
            {

                throw;
            }
        } 
        #endregion

        #region GetJobAlertStatus
        internal DataSet GetJobAlertStatus(Guid jobSeekerID, int jobAlertID, int frequencyID)
        {
            DataBaseHelper dbHelper;
            DataSet dsJobAlertStatus = new DataSet();
            try
            {
                using (SqlConnection sqlCon = new SqlConnection(base.ConnectionString))
                {
                    sqlCon.Open();
                    StoredProcedureName = StoredProcedure.Name.spSelectJobAlertStatusByID.ToString();

                    using (SqlCommand cmd = new SqlCommand(StoredProcedureName))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Connection = sqlCon;

                        SqlParameter paramJobSeekerID = cmd.Parameters.Add("@JobSeekerID", SqlDbType.UniqueIdentifier);
                        paramJobSeekerID.Direction = ParameterDirection.Input;
                        paramJobSeekerID.Value = jobSeekerID;

                        SqlParameter paramJobAlertID = cmd.Parameters.Add("@JobAlertID", SqlDbType.Int);
                        paramJobAlertID.Direction = ParameterDirection.Input;
                        paramJobAlertID.Value = jobAlertID;

                        SqlParameter paramFrequencyID = cmd.Parameters.Add("@FrequencyID", SqlDbType.Int);
                        paramFrequencyID.Direction = ParameterDirection.Input;
                        paramFrequencyID.Value = frequencyID;

                        cmd.ExecuteNonQuery();
                        SqlDataAdapter adp = new SqlDataAdapter(cmd);
                        dsJobAlertStatus = new DataSet();
                        adp.Fill(dsJobAlertStatus);

                    }
                    sqlCon.Close();
                }

            }
            catch (Exception ex)
            {
            }
            return dsJobAlertStatus;
        } 
        #endregion
    }
}
